package com.googlecode.garbagecan.dbtest.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class TestOracle {

	//create table test1(id varchar2(255), value varchar2(255));
	
	public static void main(String[] args) throws Exception {
		TestOracle t = new TestOracle();
		t.testConnection();
		//t.testStatement();
		//t.testPreparedStatement();
	}
	
	private void testConnection() throws Exception {
		long lstart = System.currentTimeMillis();
		try {
			Connection conn = getConnection();
			
			System.out.println("before sleep");
			Thread.sleep(30 * 1000);
			System.out.println("after sleep");
			
			conn.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			long lend = System.currentTimeMillis();
			long diff = lend - lstart;
			System.out.println(diff / 1000);
		}
	}
	
	private void testStatement() throws Exception {
		long lstart = System.currentTimeMillis();
		try {
			Connection conn = getConnection();
			
			System.err.println("---before sleep");
			System.out.println("---before sleep");
			Thread.sleep(5000); // pull cable
			System.out.println("---after sleep");
			System.err.println("---after sleep");
			
			System.out.println("--- before execute");
			Statement stmt = conn.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery("select count(*) from test1");
			//stmt.executeUpdate("insert into test1(id,value)values('1','11')");
			System.out.println("--- after execute");
			
			System.out.println("--- before commit");
			conn.commit();
			System.out.println("--- after commit");
			
			conn.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			long lend = System.currentTimeMillis();
			long diff = lend - lstart;
			System.out.println(diff / 1000);
		}
	}
	private void testPreparedStatement() throws Exception {
		long lstart = System.currentTimeMillis();
		try {
			Connection conn = getConnection();
			
			System.out.println("---before sleep");
			Thread.sleep(5000); // pull cable
			System.out.println("---after sleep");
			
			System.out.println("---before execute");
			String sql = "insert into test1(id,value)values(?,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setQueryTimeout(5);
			for (int j = 0; j < 10; j++) {
				ps.setObject(1, "" + j);
				ps.setObject(2, "" + j);
				ps.addBatch();
			}
			ps.executeBatch();
			ps.clearBatch();
			System.out.println("---after execute");
			
			System.out.println("--- before commit");
			conn.commit();
			System.out.println("--- after commit");
			
			conn.close();
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			long lend = System.currentTimeMillis();
			long diff = lend - lstart;
			System.out.println(diff / 1000);
		}
	}

	private Connection getConnection() throws SQLException,
			ClassNotFoundException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		String url = "jdbc:oracle:thin:@172.17.18.213:1521:xe";
		//String url = "jdbc:oracle:thin:@perf01:1521:orcl";
		DriverManager.setLoginTimeout(10);
		Connection conn = DriverManager.getConnection(url, "fkong", "fkong");
		
		return conn;
	}
}
